﻿-- =============================================
-- Script Template
-- =============================================
USE [DB_Ex]
GO

/****** Object:  StoredProcedure [dbo].[sp_Axapta_GetDocsMovs_TopRec]    Script Date: 12/05/2011 10:42:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Axapta_GetDocsMovs_TopRec] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
Declare @dt as datetime;
SELECT @dt = DATEADD(month, -3, SYSDATETIME());
Declare @dtFrom as date;
Declare @dtFrom2 as datetime;
SELECT @dt = DATEADD(day, (DAY(@dt)*-1+1), @dt);
select @dtFrom = CAST(@dt as date);
select @dtFrom2 = CAST(@dtFrom as datetime);

SELECT  
	v.INTERNALINVOICEID AS N
	, v.PURCHRETURNTYPE AS НомерДок
	, v.PURCHRETURNFACTUREID AS ТипВозврат
	, CONVERT(varchar(20), CONVERT(datetime, v.PURCHRETURNFACTUREDATE), 120) AS Дата
	, CONVERT(varchar(20), CONVERT(datetime, v.INVOICEDATE), 120) AS ФактураДата
	, v.INVOICEDATE AS ДатаДокумента
	, v.RCONTRACTACCOUNT AS Договор
	, v.INVOICEID AS НомерВх
	, v.INCLTAX AS ВклНДС
	, v.SUMTAX AS СуммаДокНДС
	, v.INVOICEAMOUNT AS СуммаДок
	, p.INVENTLOCATIONID AS Склад
	, t.NAMEALIAS AS Поставщик
	, i.NAME AS stock
	, v.PURCHASETYPE AS типдвижения
FROM
	[DB02].[MAIN].[dbo].[VENDINVOICEJOUR] AS v 
	INNER JOIN [DB02].[MAIN].[dbo].[PURCHTABLE] AS p ON v.DATAAREAID = p.DATAAREAID AND v.PURCHID = p.PURCHID 
    INNER JOIN [DB02].[MAIN].[dbo].[RCONTRACTTABLE] AS r ON v.RCONTRACTACCOUNT = r.RCONTRACTACCOUNT AND v.DATAAREAID = r.DATAAREAID 
    INNER JOIN [DB02].[MAIN].[dbo].[VENDTABLE] AS t ON v.INVOICEACCOUNT = t.ACCOUNTNUM AND v.DATAAREAID = t.DATAAREAID 
    INNER JOIN [DB02].[MAIN].[dbo].[INVENTLOCATION] AS i ON p.INVENTLOCATIONID = i.INVENTLOCATIONID AND p.DATAAREAID = i.DATAAREAID
WHERE
	    (v.RCONTRACTACCOUNT <> '')
	AND (v.INVOICEDATE >= @dtFrom2)
    AND (v.CREDITNOTE = 0) 
    AND (v.INTERNALINVOICEIDRETURN = '') 
    AND (v.DATAAREAID = 'dat')
ORDER BY ФактураДата DESC, Дата

END


GO


